﻿--- Eliminar las funciones existentes sin importar el numero ni tipo de parametros
SELECT * FROM "DropFunction_AWIL"('FuncSeguridadUsuario');
--- crear una nueva funcion
CREATE FUNCTION "FuncSeguridadUsuario"(
	 IN	"@OP" CHARACTER VARYING(64)
	,IN	"@paramAux" TEXT
	,INOUT	"@id" INTEGER
	,INOUT	"@fechaGrab" timestamp
	,INOUT	"@activo" boolean
	,INOUT	"@codUsuario" character varying(32)
	,INOUT	"@nombreUsuario" character varying(64)
	,INOUT	"@login" character varying(32)
	,INOUT	"@password" character varying(128)
)RETURNS 
	SETOF RECORD
AS $$ 
DECLARE
	Auxiliar TEXT;	
	pagina INTEGER;
	numRegistrosPorPagina INTEGER;
	campoFiltroOrden character varying(64);
	valorFiltro character varying(64);
BEGIN

	/*SELECT SUPERVISORES*/
	IF "@OP"='selectSupervisores' THEN
		RETURN	QUERY 
		SELECT	t1."id",t1."fechaGrab",t1."activo",t1."codUsuario",t1."nombreUsuario",t1."login",t1."password" 
		FROM	"SeguridadUsuario" t1
			inner join "SeguridadUsuarioRol" t2
				ON t1."codUsuario"=t2."codUsuario"
			INNER JOIN "SeguridadParametro" t3
				ON  t2."codRol"=t3.valor
				AND t3."codParametro"='RolSupervisores'
		ORDER BY t1."id";
	RETURN;
	END IF;
	
	/*SELECT ESTIBADORES*/
	IF "@OP"='selectEstibadores' THEN
		RETURN	QUERY 
		SELECT	t1."id",t1."fechaGrab",t1."activo",t1."codUsuario",t1."nombreUsuario",t1."login",t1."password" 
		FROM	"SeguridadUsuario" t1
			inner join "SeguridadUsuarioRol" t2
				ON t1."codUsuario"=t2."codUsuario"
			INNER JOIN "SeguridadParametro" t3
				ON  t2."codRol"=t3.valor
				AND t3."codParametro"='RolEstibadores'
		ORDER BY t1."id";
	RETURN;
	END IF;
	
	/*LOGIN*/
	IF "@OP"='login' THEN

		--- Verifico si existe el login
		IF NOT EXISTS (SELECT 1 FROM "SeguridadUsuario" WHERE upper("login")=upper("@login")) THEN
			Auxiliar = 'el usuario "' || "@login" || '" NO existe, verifique.';
			RAISE EXCEPTION '%', Auxiliar;
		END IF;

		--- Verifico si la contraseña es valida
		IF NOT EXISTS (SELECT 1 FROM "SeguridadUsuario" WHERE upper("login")=upper("@login") and "password"="@password") THEN
			Auxiliar = 'Contrasena incorrecta, verifique.';
			RAISE EXCEPTION '%', Auxiliar;
		END IF;

		--- Verifico si el usuario esta activo
		IF NOT EXISTS (SELECT 1 FROM "SeguridadUsuario" WHERE upper("login")=upper("@login") and "password"="@password" and "activo"=true) THEN
			Auxiliar = 'El usuario esta Inactivo, verifique.';
			RAISE EXCEPTION '%', Auxiliar;
		END IF;

		--- Verifico si el Rol del usuario esta habilitado para ingresar al sistema
		IF NOT EXISTS (
				SELECT 
					1
				FROM 
					"SeguridadUsuario" t1 
					inner join "SeguridadUsuarioRol" t2 
						on t1."codUsuario"=t2."codUsuario" 
					inner join "SeguridadRol" t3
						on t2."codRol"=t3."codRol"
				WHERE 
					t3."activo"=true
					and upper(t1."login")=upper("@login")
			) THEN
			Auxiliar = 'El rol asociado al Usuario NO esta habilitado para ingresar al sistema, verifique.';
			RAISE EXCEPTION '%', Auxiliar;
		END IF;

		-- Obtengo el codigo del usuario
		Auxiliar = (select "codUsuario" from "SeguridadUsuario" where upper("login")=upper("@login"));
		
		--- grabo el inicio de session en el log		
		PERFORM * FROM "FuncSeguridadLogEventos"('insert',NULL,NULL,NULL,Auxiliar,"@paramAux",'INICIO DE SESION','El usuario ' || "@login" || ' inicio sesion desde la WEB');
		
		RETURN	QUERY
		SELECT	"id","fechaGrab","activo","codUsuario","nombreUsuario","login","password" 
		FROM	"SeguridadUsuario"
		WHERE	upper("login")=upper("@login");
	RETURN;
	END IF;


	/*SELECT BY ID*/
	IF "@OP"='selectById' THEN
		RETURN	QUERY
		SELECT	"id","fechaGrab","activo","codUsuario","nombreUsuario","login","password" 
		FROM	"SeguridadUsuario"
		WHERE	"id"="@id";
	RETURN;
	END IF;


	/*SELECT BY Codigo*/
	IF "@OP"='selectByCodigo' THEN
		RETURN	QUERY
		SELECT	"id","fechaGrab","activo","codUsuario","nombreUsuario","login","password" 
		FROM	"SeguridadUsuario"
		WHERE	"codUsuario"="@codUsuario";
	RETURN;
	END IF;


	/*SELECT TODOS*/
	IF "@OP"='selectAll' THEN
		--- obtengo la pagina
		pagina = coalesce(CAST((string_to_array("@paramAux",'|'))[1] as INTEGER),0); 
		--- obtengo el numero de registros por pagina
		numRegistrosPorPagina = coalesce(CAST((string_to_array("@paramAux",'|'))[2] as INTEGER),-1); 
		--- obtengo el campo por el cual se ordenara y filtrara la info
		campoFiltroOrden = coalesce(trim((string_to_array("@paramAux",'|'))[3]),''); 
		--- obtengo el valor con el cual se realizara el filtro (sino se selecciono ningun campo, inicializo el valor con '')
		valorFiltro = CASE WHEN campoFiltroOrden='' THEN '' ELSE coalesce(trim((string_to_array("@paramAux",'|'))[4]),'') END;

		if valorFiltro<>'' THEN
			IF campoFiltroOrden='activo' THEN
				--- si se selecciono el campo "activo", formateo el campo valor (true, false)
				valorFiltro=CASE WHEN lower(valorFiltro)='si' OR lower(valorFiltro)='s' THEN 'true'  WHEN lower(valorFiltro)='no' OR lower(valorFiltro)='n' THEN 'false' ELSE '' END;
			ELSE
				--- si se definio un valor para filtrar, le agrego los caracteres de comodin para que la busqueda sea mas flexible
				
				valorFiltro= '%' ||  valorFiltro || '%';
			END IF;
				
		END IF;
		
		--- con esta sentencia muestro todos los resultados de la tabla
		if numRegistrosPorPagina<0 then 
			numRegistrosPorPagina=1000000;
		end if;

		
		RETURN	QUERY 
		SELECT	"id","fechaGrab","activo","codUsuario","nombreUsuario","login","password" 
		FROM	"SeguridadUsuario"		
		WHERE
			(valorFiltro<>'' AND campoFiltroOrden='codUsuario' AND "codUsuario" ILIKE valorFiltro)
			OR
			(valorFiltro<>'' AND campoFiltroOrden='nombreUsuario' AND "nombreUsuario" ILIKE valorFiltro)
			OR
			(valorFiltro<>'' AND campoFiltroOrden='login' AND "login" ILIKE valorFiltro)
			OR
			(valorFiltro<>'' AND campoFiltroOrden='activo' AND "activo" = CAST(valorFiltro AS BOOLEAN))
			OR
			valorFiltro=''
		ORDER BY 
			CASE
				WHEN campoFiltroOrden='codUsuario' THEN "codUsuario"
				WHEN campoFiltroOrden='nombreUsuario' THEN "nombreUsuario"
				WHEN campoFiltroOrden='login' THEN "login"
				WHEN campoFiltroOrden='activo' THEN CAST("activo" AS  character varying(1))
			END ASC
		       ,"id" ASC
		LIMIT 
			numRegistrosPorPagina 
		OFFSET 
			numRegistrosPorPagina*pagina;
	RETURN;
	END IF;

	/*INSERTAR NUEVO REGISTRO*/
	IF "@OP"='insert' THEN

		--- Verifico que no exista  con ese CODIGO
		IF EXISTS (SELECT 1 FROM "SeguridadUsuario" WHERE "codUsuario"="@nombreUsuario") THEN
			Auxiliar = 'Ya existe un Usuario con el codigo "' || "@codUsuario" || '", verifique.';
			RAISE EXCEPTION '%', Auxiliar;
		END IF;

		--- Verifico que no exista con ese NOMBRE
		IF EXISTS (SELECT 1 FROM "SeguridadUsuario" WHERE "nombreUsuario"="@nombreUsuario") THEN
			Auxiliar = 'Ya existe un Usuario con el Nombre "' || "@nombreUsuario" || '", verifique.';
			RAISE EXCEPTION '%', Auxiliar;
		END IF;

		--- Verifico que no exista con ese LOGIN
		IF EXISTS (SELECT 1 FROM "SeguridadUsuario" WHERE "login"="@login") THEN
			Auxiliar = 'Ya existe un Usuario con el Login "' || "@login" || '", verifique.';
			RAISE EXCEPTION '%', Auxiliar;
		END IF;

		-- Grabo el registro
		INSERT INTO "SeguridadUsuario" ("activo","codUsuario","nombreUsuario","login","password")
		VALUES	 (coalesce("@activo",TRUE),"@codUsuario","@nombreUsuario","@login","@password");

		RETURN	QUERY
		SELECT	"id","fechaGrab","activo","codUsuario","nombreUsuario","login","password" 
		FROM	"SeguridadUsuario"
		ORDER BY id DESC LIMIT 1;
	RETURN;
	END IF;

	/*ACTUALIZAR REGISTRO*/
	IF "@OP"='update' THEN
		--- Verifico que no exista  con el nuevo CODIGO
		IF EXISTS (SELECT 1 FROM "SeguridadUsuario" WHERE "codUsuario"="@codUsuario" AND "id"<>"@id") THEN
			Auxiliar='Ya existe Otro Usuario con el codigo "' || "@codUsuario" || '", verifique.';
			RAISE EXCEPTION '%',Auxiliar;
		END IF;

		--- Verifico que no exista  con ese NOMBRE
		IF EXISTS (SELECT 1 FROM "SeguridadUsuario" WHERE "nombreUsuario"="@nombreUsuario" AND "id"<>"@id") THEN
			Auxiliar='Ya existe Otro Usuario con el Nombre "' || "@nombreUsuario" || '", verifique.';
			RAISE EXCEPTION '%',Auxiliar;
		END IF;

		--- Verifico que no exista  con ese NOMBRE
		IF EXISTS (SELECT 1 FROM "SeguridadUsuario" WHERE "login"="@login" AND "id"<>"@id") THEN
			Auxiliar='Ya existe Otro Usuario con el Login "' || "@login" || '", verifique.',"@Login";
			RAISE EXCEPTION '%',Auxiliar;
		END IF;

		--- si se va a cambiar el codigo,Verifico que no se encuentre en uso
		IF 
			EXISTS (SELECT 1 FROM "SeguridadUsuario" WHERE "codUsuario"<>"@codUsuario" and "id"="@id")
			AND
			EXISTS (SELECT 1 FROM "SeguridadUsuarioRol" t1 inner join "SeguridadUsuario" t2 on t1."codUsuario"=t2."codUsuario" and t2.id="@id") 
		THEN
			Auxiliar = 'El Usuario "' || "@nombreUsuario" || '" Ya se encuentra en uso, NO puede cambiar el codigo.';
			RAISE EXCEPTION '%', Auxiliar;
		END IF;

		--- Actualizo el registro
		UPDATE	
			"SeguridadUsuario" 
		SET	
			 "activo"=coalesce("@activo","activo")
			,"fechaUltMod"=NOW() 
			,"codUsuario"="@codUsuario"
			,"nombreUsuario"="@nombreUsuario"			
			,"login"="@login"			
			,"password"= CASE WHEN trim("@password")='' THEN "password" ELSE "@password" END
		WHERE	
			"id"="@id";

	RETURN;
	END IF;

	/*ELIMINAR REGISTRO*/
	IF "@OP"='delete' THEN

		--- si se va a cambiar el codigo,Verifico que no se encuentre en uso
		IF 
			EXISTS (SELECT 1 FROM "SeguridadUsuarioRol" t1 inner join "SeguridadUsuario" t2 on t1."codUsuario"=t2."codUsuario" and t2.id="@id") 
		THEN
			Auxiliar = 'El Usuario ya se encuentra en uso, NO puede ser Eliminado.';
			RAISE EXCEPTION '%', Auxiliar;
		END IF;

		--- Borro el registro
		DELETE FROM "SeguridadUsuario" 
		WHERE "id"="@id";

	RETURN;
	END IF;


--- Controlo que se haya digitado el OP correctamente
	IF "@OP" IS NOT NULL THEN
		Auxiliar='El OP "' || "@OP" || '" No existe, verifique.';
		RAISE EXCEPTION '%',Auxiliar;
	RETURN;
	END IF;
END;
$$ LANGUAGE 'plpgsql';